﻿Imports ISNet.WebUI.WebGrid
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports log4net


Partial Class StrategyMapping
    Inherits System.Web.UI.Page
    Public ScripText As String = ""

    Private Shared logger As ILog = LogManager.GetLogger("FileAppender")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        ScreenName.Text = "ระบบจัดทำงบประมาณ  >>  ตั้งค่าระบบ >> Fund Center"
        'Session("users") = "bb0011"
        If Session("users") Is Nothing Then
            Response.Redirect("LoginForm.aspx")
        End If
        
        If IsPostBack = False Then
            PlanYearsTxt.Text = Session("PlanYear")
            Session("PlanYearTxt") = PlanYearsTxt.Text
            Session("searchtext") = "and PlanYear=" & PlanYearsTxt.Text
            'Else
            '    PlanYearsTxt.Text = Session("PlanYearTxt")
        End If

    End Sub

    Protected Sub WebGrid1_InitializeDataSource(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.DataSourceEventArgs)
        Dim adapter As New StrategyMappingDSTableAdapters.StrategyMappingTableAdapter
        Dim dtTable As New StrategyMappingDS.StrategyMappingDataTable
        Dim sql As String = ""
        Dim prefil As String = ""
        prefil = Session("prefilter")
        If Session("searchtext") = "" Then
            e.DataSource = dtTable
            Exit Sub
        End If

        'If IsPostBack = True And Session("Search") = True Then

        sql = " "
        sql += " WHERE 1 = 1 "
        sql += Session("searchtext")

        sql += " ORDER BY PlanYear, FundCenter, CommitmentItem, FundedProgram "

        adapter.GetDataByFilter(dtTable, sql)
        e.DataSource = dtTable
        Session("count_data") = dtTable.Rows.Count

        dtTable.Columns("PlanYear").AllowDBNull = True
        dtTable.Columns("FundCenter").AllowDBNull = True
        dtTable.Columns("CommitmentItem").AllowDBNull = True
        dtTable.Columns("FundedProgram").AllowDBNull = True
        dtTable.Columns("Strategy").AllowDBNull = True

        'Else

        'sql = "select * from Projects "
        'sql += " WHERE 1 = 1 "
        'sql += " and PlanYear like '" & Val(Session("PlanYearTxt")) & "'"

        'If prefil <> "" Then
        '    sql += " AND FundCenter_FundCenterCode IN (" & prefil & ") "
        'End If


        'sql += " ORDER BY ProjectCode "

        'adapter.GetDataByFilter(dtTable, sql)

        'e.DataSource = dtTable
        'Session("count_data") = dtTable.Rows.Count

        'dtTable.Columns("PlanYear").AllowDBNull = True
        'dtTable.Columns("ProjectCode").AllowDBNull = True
        'dtTable.Columns("Description").AllowDBNull = True


        'End If
        ' Response.Write(sql)

    End Sub

    Protected Sub WebGrid1_InitializeRow(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.RowEventArgs)
        ' don't edit column
        e.Row.Cells(0).ForceNoEdit = True
        e.Row.Cells(1).ForceNoEdit = True
        e.Row.Cells(2).ForceNoEdit = True
    End Sub

    Protected Sub WebGrid1_InitializePostBack(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.PostbackEventArgs)
        If e.Action = PostBackAction.Custom Then
            Dim id As String = TryCast(Request("Id"), String)

            Dim adapter As New StrategyMappingDSTableAdapters.StrategyMappingTableAdapter


            Try
                Dim used As Integer = 0
                If used = 0 Then
                    adapter.Delete(id)
                Else
                    WebGrid1.ClientAction.Alert("ไม่สามารถลบข้อมูลได้เนื่องจากข้อมูลถูกนำไปใช้งานแล้ว")
                End If
            Catch ex As Exception
                WebGrid1.ClientAction.Alert("ไม่สามารถลบข้อมูลได้กรุณาติดต่อผู้ดูแลระบบ")
            End Try

            ScripText = ""
            WebGrid1.ClientAction.Refresh()
        End If

    End Sub

    Protected Sub SetNotnull(ByVal dataTable As DataTable, culumnName As String)
        Dim Row() As Data.DataRow
        Row = dataTable.Select(culumnName + " is NULL")
        For i As Integer = Row.Count - 1 To 0 Step -1
            Row(i)(culumnName) = ""
        Next
    End Sub

    Protected Sub SetUser(ByVal dataTable As DataTable, culumnName As String)
        dataTable.Columns.Remove(culumnName)
        Dim dc As New DataColumn()
        dc.ColumnName = culumnName
        dc.DefaultValue = Session("users")
        dc.Unique = False
        dataTable.Columns.Add(dc)
    End Sub

    Protected Sub GenerateBtn_Click(ByVal sender As Object, e As ImageClickEventArgs) Handles GenerateBtn.Click

        Dim adapter As New StrategyMappingDSTableAdapters.StrategyMappingTableAdapter
        Dim dtTable As New StrategyMappingDS.StrategyMappingDataTable
        Dim sql As String = ""
        Try
            sql += " WHERE PlanYear =  " & PlanYearsTxt.Text
            adapter.DeleteByYear(PlanYearsTxt.Text)
            adapter.GetDataToImport(dtTable, sql)

            SetNotnull(dtTable, "FundCenter")
            SetNotnull(dtTable, "CommitmentItem")
            SetNotnull(dtTable, "FundedProgram")
            SetNotnull(dtTable, "Strategy")
            SetUser(dtTable, "CreateBy")
            SetUser(dtTable, "UpdateBy")


            Dim connectionstring = ConfigurationManager.ConnectionStrings("BudDbConnectionString").ConnectionString


            Using bulkCopy As New SqlBulkCopy(connectionstring)
                bulkCopy.DestinationTableName = "StrategyMapping"

                bulkCopy.ColumnMappings.Add("FundCenter", "FundCenter")
                bulkCopy.ColumnMappings.Add("CommitmentItem", "CommitmentItem")
                bulkCopy.ColumnMappings.Add("PlanYear", "PlanYear")
                bulkCopy.ColumnMappings.Add("FundedProgram", "FundedProgram")
                bulkCopy.ColumnMappings.Add("Strategy", "Strategy")
                bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate")
                bulkCopy.ColumnMappings.Add("CreateBy", "CreateBy")
                bulkCopy.ColumnMappings.Add("UpdateDate", "UpdateDate")
                bulkCopy.ColumnMappings.Add("UpdateBy", "UpdateBy")
                ' MessageBox.Show("Data Exoprted To Sql Server Succefully");
                bulkCopy.WriteToServer(dtTable)
                'Page.RegisterClientScriptBlock("OnLoad", "<script>$(document).ready(function() {  WarningDialog('" + "Import ข้อมูลเสร็จสิ้น จำนวน " + countInsert.ToString("N0") + "รายการ " + "') });</script>");

            End Using

            adapter.UpdateStrategy()


            'Using con = New SqlConnection(connectionstring)
            '    ' Create connection with automatic disposal
            '    con.Open()
            '    Using tran = con.BeginTransaction()
            '        ' Open a transaction
            '        ' Create command with parameters  (DO NOT PUT VALUES IN LINE!!!!!)
            '        sql = "INSERT INTO [dbo].[StrategyMapping] ([PlanYear], [FundCenter], [CommitmentItem], [FundedProgram], [Strategy]) VALUES (@PlanYear, @FundCenter, @CommitmentItem, @FundedProgram, @Strategy)"
            '        Dim cmd = New SqlCommand(sql, con)
            '        cmd.Parameters.AddWithValue("PlanYear", dtTable(0))
            '        cmd.Parameters.AddWithValue("FundCenter", dtTable(1))
            '        cmd.Parameters.AddWithValue("CommitmentItem", dtTable(2))
            '        cmd.Parameters.AddWithValue("FundedProgram", dtTable(3))
            '        cmd.Parameters.AddWithValue("Strategy", dtTable(4))

            '        cmd.ExecuteNonQuery()
            '        ' Insert Record
            '        ' commit transaction
            '        tran.Commit()
            '    End Using
            'End Using

        Catch ex As Exception
            WebGrid1.ClientAction.Alert("ไม่สามารถนำเข้าข้อมูลได้กรุณาติดต่อผู้ดูแลระบบ")
        End Try

        ScripText = ""
        'SearhBtn_Click(sender, e)
        WebGrid1.ClientAction.Refresh()
        WebGrid1.ClearCachedDataSource()
        WebGrid1.RebindDataSource()


    End Sub

    Protected Sub SearhBtn_Click(sender As Object, e As ImageClickEventArgs) Handles SearhBtn.Click
        Session("PlanYearTxt") = Val(PlanYearsTxt.Text)
        Session("searchtext") = ""
        'If FundCenter.Value = "" Then
        '    ScripText = "<script> alert('กรุณาเลือก Fundcenter');</script>"
        '    Exit Sub
        'End If
        Session("searchtext") += " and PlanYear like '" & Val(Session("PlanYearTxt")) & "'"

        If FundCenter.Value <> "" Then
            Session("searchtext") += " and  FundCenter  = '" & FundCenter.Value & "'"
        End If
        If FundedProgram.Value <> "" Then
            Session("searchtext") += " and  FundedProgram  = '" & FundedProgram.Value & "'"
        End If
        If Cmmts.Value <> "" Then
            Session("searchtext") += " and  CommitmentItem  = '" & Cmmts.Value & "'"
        End If
        If Strategy.Value <> "" Then
            Session("searchtext") += " and  Strategy  = '" & Strategy.Value & "'"
        End If

        WebGrid1.ClearCachedDataSource()
        WebGrid1.RebindDataSource()
        Session.Remove("Search")
        Session.Add("Search", True)


    End Sub

    Protected Sub FundCenter_InitializeDataSource(sender As Object, e As ISNet.WebUI.WebCombo.DataSourceEventArgs) Handles FundCenter.InitializeDataSource
        Dim adap As New StrategyMappingDSTableAdapters.FundCentersTableAdapter
        Dim dt As New StrategyMappingDS.FundCentersDataTable

        Dim sql = ""
        Dim prefil As String = ""
        prefil = Session("prefilter")
        sql += " WHERE 1 = 1 "
        sql += " and  PlanYear = " & Val(Session("PlanYearTxt"))
        sql += " ORDER BY FundCenterCode ASC"
        adap.GetDataByFilter(dt, sql)

        'dt.Rows.Add("", "โครงการกลาง", "1", "0")


        dt.DefaultView.Sort = "FundCenterCode"
        e.DataSource = dt

        If dt.Count = 1 Then
            FundCenter.Value = dt.Item(0).FundCenterCode
        End If

    End Sub

    Protected Sub Cmmts_InitializeDataSource(sender As Object, e As ISNet.WebUI.WebCombo.DataSourceEventArgs) Handles FundCenter.InitializeDataSource
        Dim adap As New StrategyMappingDSTableAdapters.CmmtsTableAdapter
        Dim dt As New StrategyMappingDS.CmmtsDataTable


        Dim sql = ""
        Dim prefil As String = ""
        prefil = Session("prefilter")
        sql += " WHERE 1 = 1 "
        sql += " and  PlanYear = " & Val(Session("PlanYearTxt"))
        sql += " ORDER BY CmmtCode ASC"
        adap.GetDataByFilter(dt, sql)

        'dt.Rows.Add("", "โครงการกลาง", "1", "0")


        dt.DefaultView.Sort = "CmmtCode"
        e.DataSource = dt

        If dt.Count = 1 Then
            FundCenter.Value = dt.Item(0).CmmtCode

        End If

    End Sub

    Protected Sub FundedProgramProjects_InitializeDataSource(sender As Object, e As ISNet.WebUI.WebCombo.DataSourceEventArgs) Handles FundCenter.InitializeDataSource
        Dim adap As New StrategyMappingDSTableAdapters.FundedProgramProjectsTableAdapter
        Dim dt As New StrategyMappingDS.FundedProgramProjectsDataTable

        Dim sql = ""
        Dim prefil As String = ""
        prefil = Session("prefilter")
        sql += " WHERE 1 = 1 "
        sql += " and Version = '0' AND  PlanYear = " & Val(Session("PlanYearTxt"))
        sql += " ORDER BY FundedProgram ASC"
        adap.GetDataByFilter(dt, sql)

        'dt.Rows.Add("", "โครงการกลาง", "1", "0")


        dt.DefaultView.Sort = "FundedProgram"
        e.DataSource = dt

        If dt.Count = 1 Then
            FundCenter.Value = dt.Item(0).FundedProgram

        End If

    End Sub

    Protected Sub Strategy_InitializeDataSource(sender As Object, e As ISNet.WebUI.WebCombo.DataSourceEventArgs) Handles FundCenter.InitializeDataSource
        Dim adap As New StrategyMappingDSTableAdapters.StrategyTableAdapter
        Dim dt As New StrategyMappingDS.StrategyDataTable

        Dim sql = ""
        Dim prefil As String = ""
        prefil = Session("prefilter")
        sql += " WHERE 1 = 1 "
        sql += " and   PlanYear = " & Val(Session("PlanYearTxt"))
        sql += " ORDER BY StrategyCode ASC"
        adap.GetDataByFilter(dt, sql)

        'dt.Rows.Add("", "โครงการกลาง", "1", "0")


        dt.DefaultView.Sort = "StrategyCode"
        e.DataSource = dt

        If dt.Count = 1 Then
            FundCenter.Value = dt.Item(0).StrategyCode
        End If

    End Sub

    Protected Sub ImageButton1_Click(sender As Object, e As ImageClickEventArgs) Handles ImageButton1.Click

        ScripText = "<script> popupkrajay3('" & 1 & "','" & 0 & "','" & PlanYearsTxt.Text & "','" & 0 & "');</script>"
    End Sub

   

End Class